const conn = require('../db/config')

exports.all = (req,res) => {
    var sql = 'select * from orderlist'
    var page = req.query.offset || 0
    var limit = req.query.limit || 10
    conn.query(sql,(err,data)=>{
        if(err){
            return res.send('错误')
        }
    var sqllen = 'SELECT num,orderpersonname,frombus,telphone,date_format(submittime,"%Y-%m-%d") as submittime,sumprice,ishandle FROM `orderlist` LIMIT '+ limit +' offset '+ page
    conn.query(sqllen,(err,result) => {
        if(err) return console.log(err)
        if(result.length) {
            res.send({data:result,total:data.length})
        }
    })
})
}

exports.search = (req,res) => {
    //num||submittime(数据库字段名)
    var datatype = req.query.type
    var data = req.query.search
    var timearr = req.query.timeranges
    if(data!=='') data = "'"+data+"'"
    if(timearr!==''){
        var start = "'"+timearr[0]+"'"
        var end = "'"+timearr[1]+"'"
        var sqllen = "SELECT num,orderpersonname,frombus,telphone,date_format(submittime,'%Y-%m-%d') as submittime,sumprice,ishandle FROM `orderlist` WHERE submittime between "+start+" and "+end
    } 
    var sql = 'SELECT num,orderpersonname,frombus,telphone,date_format(submittime,"%Y-%m-%d") as submittime,sumprice,ishandle FROM `orderlist` WHERE '+datatype+' = '+data
    if((datatype===''||data==='')&&timearr!=='')
    conn.query(sqllen,(err,result) => {
        if(err)  return console.log(err)
        if(result.length)    return res.send({data:result,status:200,total:result.length})
        else    return res.send({msg:'未找到',status:500})
    })
    else if(datatype!==''&&data!==''||timearr==='')
    conn.query(sql,(err,result) => {
        if(err)  return console.log(err)
        if(result.length)    return res.send({data:result,status:200,total:result.length})
        else    return res.send({msg:'未找到',status:500})
    })
    else{
        if(datatype === 'num') 
          conn.query(sql,(err,result) => {
            if(err) return console.log(err)
            if(result.length) {
                return res.send({data:result,status:200,total:result.length})
            }else{
                conn.query(sqllen,(err,result) => {
                    if(err)   return console.log(err)
                    if(result.length)    return res.send({data:result,status:200,total:result.length})
                    else    return res.send({msg:'未找到',status:500})
                })
            }})
        else{
            conn.query(sql,(err,result) => {
                if(err) return console.log(err)
                if(result.length) {
                    var sqll = sqllen + " and "+datatype+" = "+data
                    conn.query(sqll,(err,put) => {
                        if(err)   return console.log(err)
                        if(put.length)    return res.send({data:put,status:200,total:put.length})
                        else    return res.send({data:result,status:200})
                    })
                }
            }) 
        }
    }
}

exports.sethandledata = (req,res) => {
    //拿到多行或单行的订单数据，若是多行将各个订单数据中的sum相加【这个应该是由前端实现，前端最终返回订单的总价钱和处理人名字】
    var time = req.query.time
    var sum = req.query.sum
    var handlename = req.query.username
    //insert into handlelist set handleperson='呃',time=NOW(),sum=545;语句无误
    var sql = 'insert into handlelist set handleperson='+handlename+',time=NOW(),sum='+sum
    conn.query(sql,(err,result) => {
        if(err) return console.log(err)
        //插入成功的返回值是啥啊？？
    })
}

/*
var datatype = 'num'
var data = 'jjbashas'
var start = '2989029'
var end = '28278'
*/